package com.sxj.features.excel;

import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.util.Collection;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.sxj.base.util.ApplicationException;
import com.sxj.base.util.DateUtil;
import com.sxj.features.annotation.ExcelCell;


public class ExcelUtil {
	
	@SuppressWarnings("unchecked")
	public static void importExcel(Object list, InputStream in, Class<?> clazz) {
		if (!(list instanceof Collection)) {
			throw new ApplicationException("-1", "Object对象类型必须为要为LIST");
		}
		Collection<Object> collection = (Collection<Object>) list;
		// 解码  
//		byte[] fileByte = decode(in);
//		//将byte数组转换为InputStream
//		InputStream inputStream = byteToInputStream(fileByte);
		//Workbook workbook = new HSSFWorkbook(inputStream);//*.xls
		try {
			Workbook workbook = new XSSFWorkbook(in);// *.xlsx
			if (workbook != null) {
				for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
					Sheet sheet = workbook.getSheetAt(sheetIndex);
					if (sheet == null) {
						continue;
					}
					int rowstart = sheet.getFirstRowNum();
				    int rowEnd = sheet.getLastRowNum();
					for (int cellIndex = rowstart;cellIndex <= rowEnd; cellIndex++) {
						Object t = clazz.newInstance();
						Field[] fields = t.getClass().getDeclaredFields();
						Row row = sheet.getRow(cellIndex);
						if (row == null) {
							continue;
						}
						if (cellIndex == rowstart) {
							checkExcel(fields, row);
							continue;
						}
						for (Field field : fields) {
							ExcelCell excelCell = field.getAnnotation(ExcelCell.class);
					        if (excelCell == null) {
					        	continue;
					        }
					        field.setAccessible(true);
					        field.set(t, parseExcel(row.getCell(excelCell.index() - 1)));
						}
						collection.add(t);
					}
				}
			}
		} catch (IOException e) {
			e.printStackTrace();
		} catch (NumberFormatException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}
	}
	
	private static void checkExcel(Field[] fields, Row row) {
		for (Field field : fields) {
			ExcelCell excelCell = field.getAnnotation(ExcelCell.class);
	        if (excelCell == null) {
	        	throw new ApplicationException("-1", "模板文件错误！");
	        }
	        String dtoHead = excelCell.head();
	        String head = parseExcel(row.getCell(excelCell.index() - 1));
	        if (!dtoHead.equals(head)) {
	        	throw new ApplicationException("-1", "模板文件错误！");
	        }
		}
	}

	/**
	 * 根据单元格数据类型获取数据
	 * @param cell
	 * @return
	 */
	 public static String parseExcel(Cell cell) {
		String value = "";
	 	if (cell == null) {
	 		return value;
	 	}
		switch (cell.getCellType()) {
		case XSSFCell.CELL_TYPE_STRING:
			value = cell.getStringCellValue();
			break;
		case XSSFCell.CELL_TYPE_NUMERIC:
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				Date date = cell.getDateCellValue();
				if (date != null) {
					value = DateUtil.get4yMdHms(date);//new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);
				} else {
					value = "";
				}
			} else {
				value = new DecimalFormat("0").format(cell
						.getNumericCellValue());
			}
			break;
		case XSSFCell.CELL_TYPE_FORMULA:
			// 导入时如果为公式生成的数据则无值
			if (!cell.getStringCellValue().equals("")) {
				value = cell.getStringCellValue();
			} else {
				value = cell.getNumericCellValue() + "";
			}
			break;
		case XSSFCell.CELL_TYPE_BLANK:
			break;
		case XSSFCell.CELL_TYPE_ERROR:
			value = "";
			break;
		case XSSFCell.CELL_TYPE_BOOLEAN:
			value = (cell.getBooleanCellValue() == true ? "Y" : "N");
			break;
		default:
			value = "";
		}
		return value;
	}
	 
	@SuppressWarnings("unchecked")
	public static void exportExcel(Object list, String filePath, Class<?> clazz) {
		if (!(list instanceof Collection)) {
			throw new ApplicationException("-1", "Object对象类型必须为要为LIST");
		}
		Collection<Object> collection = (Collection<Object>) list;
		XSSFWorkbook workbook = new XSSFWorkbook();
	    Sheet sheet = workbook.createSheet("sheet name");
	    //获取参数个数作为excel列数
	    createColumeHead(clazz, sheet, workbook);
	    //写入数据
	    int index = 1;
	    for(Object object : collection)
	    {
	    	Row row = sheet.createRow(index);
	    	Field[] fields = object.getClass().getDeclaredFields();
	        //写入数据
			for (Field field : fields) {
				ExcelCell excelCell = field.getAnnotation(ExcelCell.class);
		        if (excelCell == null) {
		        	continue;
		        }
		        int n = excelCell.index() - 1;
		        row.createCell(n);
		        //通过反射获取属性的值
				try {
					field.setAccessible(true);
					Object value = field.get(object);
			        if (value == null) {
			        	continue;
			        }
			        row.getCell(n).setCellValue(value.toString());
				} catch (IllegalArgumentException e) {
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				}
			}
			index++;
	    }

	    //写到磁盘上
	    try {
	    	File file = new File(filePath);
	    	if (file.exists()) {
	    		file.delete();
	    	}
	        FileOutputStream fileOutputStream = new FileOutputStream(file);
	        workbook.write(fileOutputStream);
	        fileOutputStream.close();
	    } catch (FileNotFoundException e) {
	        e.printStackTrace();
	    } catch (IOException e) {
	        e.printStackTrace();
	    }
	}
	
	private static void createColumeHead(Class<?> clazz, Sheet sheet, XSSFWorkbook workbook) {
		try {
			Object t = clazz.newInstance();
			Field[] fields = t.getClass().getDeclaredFields();
		    //创建第一栏
		    Row row = sheet.createRow(0);
			for (Field field : fields) {
				ExcelCell excelCell = field.getAnnotation(ExcelCell.class);
		        if (excelCell == null) {
		        	continue;
		        }
		        int n = excelCell.index() - 1;
		        Cell cell = row.createCell(n);
		        cell.setCellType(Cell.CELL_TYPE_STRING);
		        sheet.setColumnWidth(n, 6000);
		        CellStyle style = workbook.createCellStyle();
		        XSSFFont font = workbook.createFont();
		        font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
//			    short color = XSSFColor.RED.index;
//			    font.setColor(color);
		        style.setFont(font);
		        //填写数据
		        row.getCell(n).setCellStyle(style);
		        row.getCell(n).setCellValue(excelCell.head());
			}
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 解码
	 * 
	 * @param str
	 * @return string
	 */
	@SuppressWarnings("restriction")
	public static byte[] decode(String str) {
		byte[] bt = null;
		try {
			sun.misc.BASE64Decoder decoder = new sun.misc.BASE64Decoder();
			bt = decoder.decodeBuffer(str);
		} catch (IOException e) {
			e.printStackTrace();
		}
		return bt;
	}
	
	/**
	 * 将byte数组转换为InputStream
	 * 
	 * @param byt
	 * @return
	 */
	public static InputStream byteToInputStream(byte[] byt) {
		InputStream input = new ByteArrayInputStream(byt);
		return input;
	}

}
